package com.yx.learn.dao.impl;

import java.util.List;

import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;

import com.yx.learn.dao.UserAuthorityDao;
import com.yx.learn.domain.Authority;
import com.yx.learn.domain.UserAuthority;

@Repository("userAuthorityDao")
public class UserAuthorityDaoImpl implements UserAuthorityDao {

	@Autowired
	@Qualifier("hibernateTemplate")
	private HibernateTemplate hibernateTemplate;

	public List<Authority> findUserAuthorityByNo(String employNo)
			throws Exception {
		// TODO Auto-generated method stub
		String sql = "select * from TAuthority a where a.id in (select ra.authorityID from TRoleAuthority ra where ra.roleID in (select r.id from TRole r where r.id in (select ur.roleID from TUserRole ur where ur.userID in (select u.id from TUser u where u.employNo='"
				+ employNo
				+ "')))) or a.id in (select ga.authorityID from TGroupAuthority ga where ga.groupID in (select g.id from TGroup g where g.id in (select ug.groupID from TUserGroup ug where ug.userID in (select u.id from TUser u where u.employNo='"
				+ employNo
				+ "')))) or a.id in (select ua.authorityID from TUserAuthority ua where ua.userID in(select u.id from TUser u where u.employNo='"
				+ employNo + "'))";
		SQLQuery authoritie = hibernateTemplate.getSessionFactory()
				.getCurrentSession().createSQLQuery(sql)
				.addEntity(Authority.class);
		@SuppressWarnings("unchecked")
		List<Authority> authorities = authoritie.list();
		return authorities;

	}

	public List<Authority> findUserAuthorityOfMenuByNo(String employNo)
			throws Exception {
		// TODO Auto-generated method stub
		String sql = "select * from TAuthority a where a.flag='menu' and (a.id in (select ra.authorityID from TRoleAuthority ra where ra.roleID in (select r.id from TRole r where r.id in (select ur.roleID from TUserRole ur where ur.userID in (select u.id from TUser u where u.employNo='"
				+ employNo
				+ "')))) or a.id in (select ga.authorityID from TGroupAuthority ga where ga.groupID in (select g.id from TGroup g where g.id in (select ug.groupID from TUserGroup ug where ug.userID in (select u.id from TUser u where u.employNo='"
				+ employNo
				+ "')))) or a.id in (select ua.authorityID from TUserAuthority ua where ua.userID in(select u.id from TUser u where u.employNo='"
				+ employNo + "')))";
		SQLQuery authoritie = hibernateTemplate.getSessionFactory()
				.getCurrentSession().createSQLQuery(sql)
				.addEntity(Authority.class);
		@SuppressWarnings("unchecked")
		List<Authority> authorities = authoritie.list();
		return authorities;
	}
	

		@SuppressWarnings("unchecked")
		public List<UserAuthority> getUserAuhtorities() throws Exception {
			// TODO Auto-generated method stub
			String hql = "From UserAuthority";
			return this.hibernateTemplate.find(hql);
		}
		
		public UserAuthority findUserAuthority(int uid, int aid) throws Exception {
			String hql = "From UserAuthority where userID=? and authorityID =?";		
			UserAuthority ua = (UserAuthority) hibernateTemplate.find(hql,new Object[] { uid, aid }).get(0);
			return ua;
		}

		@SuppressWarnings("unchecked")
		public List<Integer> getUserAuthorityUID(int uid) throws Exception {
			String sql = "select authorityID from tuserauthority where userID =" + uid+ " ";
			List<Integer> authorityID = hibernateTemplate.getSessionFactory().getCurrentSession().createSQLQuery(sql).list();
			return authorityID;
		}
		
		public void saveUserAuthority(UserAuthority userAuthority) throws Exception {
			hibernateTemplate.save(userAuthority);
		}

		public void deleteUserAuthority(UserAuthority userAuthority) throws Exception {	
			hibernateTemplate.delete(userAuthority);
		}
		

		@SuppressWarnings("unchecked")
		public UserAuthority findUserAuthorityById(int uaid) throws Exception {
			// TODO Auto-generated method stub
			String hql = "From UserAuthority where id=?";
			List<UserAuthority> list = hibernateTemplate.find(hql,uaid);
			UserAuthority userAuthority = list.isEmpty() ? null : (UserAuthority)list.get(0);
			return userAuthority;
		}

		@SuppressWarnings("unchecked")
		public List<Authority> getAuthorityByUid(int uid) throws Exception {
			// TODO Auto-generated method stub
			String sql = "select * from tauthority where id in (select authorityID from tuserauthority where userID="+uid+")";
			SQLQuery query= hibernateTemplate.getSessionFactory().getCurrentSession().createSQLQuery(sql).addEntity(Authority.class);
			List<Authority> listAuthority = query.list();
			return listAuthority;
		}


}
